package com.unidt.bridge;

import com.unidt.dbutils.IDataBase;
import com.unidt.dbutils.MySQL;
import com.unidt.hdfs.HdfsUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * 将HDFS中的数据写入MYSQL等关系型数据库
 */
public class FlushToDB {
    Logger log = LoggerFactory.getLogger(FlushToDB.class);

    /**
     * 创建指定关系数据库的连接
     * @throws Exception
     */
    public IDataBase createMySQL() throws Exception {
        IDataBase db = new MySQL();
        db.connect();
        return db;
    }

    /**
     * 将HDFS中的数据读取出来，并写入数据库
     * @param dir
     */
    public void flush(String dir, IDataBase db) {

        log.info("作业开始");
        HdfsUtils hdfs = new HdfsUtils();
        List<String> result = hdfs.readDir(dir);
        List<String> sqls = new ArrayList<String>();
        sqls.add("drop * from table final_tag_4");
        try {
            for (String row: result) {
                String[] cols = row.split("\01");
                sqls.add(rowToSQL(cols));
            }
            db.executeBatch(sqls);
            db.close();
            log.info("作业执行完毕");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将每一行语句转化成SQL，插入数据库
     * @param cols
     * @return
     */
    public String rowToSQL(String[] cols) {
        StringBuffer sqlbuf = new StringBuffer();
        sqlbuf.append("insert into final_tag_4 values(");
        // user_id
        sqlbuf.append(stringCol(cols[0])).append(",");
        // gender
        sqlbuf.append(cols[1]).append(",");
        // age
        sqlbuf.append(cols[2]).append(",");
        // login_freq
        sqlbuf.append(cols[3]).append(",");
        // open_count
        sqlbuf.append(cols[4]).append(",");
        // login_night
        sqlbuf.append(cols[5]).append(",");
        // buy_mean
        sqlbuf.append(cols[6]).append(",");
        // risk_index
        sqlbuf.append(cols[7]).append(",");
        // bank_trust_index
        sqlbuf.append(cols[8]).append(",");
        // rtqw_index
        sqlbuf.append(cols[9]).append(",");
        // payment_index
        sqlbuf.append(cols[10]).append(",");
        // social_act_index
        sqlbuf.append(cols[11]).append(",");
        // swjj_index
        sqlbuf.append(cols[12]).append(",");
        // hvalue_customer_index
        sqlbuf.append(cols[13]).append(",");
        // gslc_index
        sqlbuf.append(cols[14]).append(",");
        // nhg_index
        sqlbuf.append(cols[15]).append(",");
        // fund_index
        sqlbuf.append(cols[16]).append(",");
        // lsfx_index
        sqlbuf.append(cols[17]).append(",");
        // qbfe_index
        sqlbuf.append(cols[18]).append(")");
        return sqlbuf.toString();
    }

    public String stringCol(String value) {
        return new StringBuffer().append("'").append(value).append("'").toString();
    }
    public static void main(String[] args) throws Exception {
        FlushToDB   todb = new FlushToDB();
        todb.flush(args[0], todb.createMySQL());
    }
}
